package com.jinwei.qunguanjia.common.utils.poi;

import com.jinwei.qunguanjia.common.constant.Constants;
import com.jinwei.qunguanjia.common.utils.JxlsUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.apache.commons.jexl3.JexlBuilder;
import org.apache.commons.jexl3.JexlEngine;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jxls.area.Area;
import org.jxls.builder.AreaBuilder;
import org.jxls.builder.xls.XlsCommentAreaBuilder;
import org.jxls.command.GridCommand;
import org.jxls.common.CellRef;
import org.jxls.common.Context;
import org.jxls.expression.JexlExpressionEvaluator;
import org.jxls.transform.Transformer;
import org.jxls.util.JxlsHelper;
import org.jxls.util.TransformerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * createUser:JAVA_TOM
 * FileName: ExcelServiceImpl
 * Author:   14562
 * Date:     2019/4/10 13:19
 * Description:
 * https://blog.csdn.net/baidu_36342103/article/details/93160916
 * https://wenku.baidu.com/view/5c457936b90d6c85ec3ac659.html
 * https://www.cnblogs.com/dw3306/p/11098841.html
 */
@Component
@Slf4j
public class ExcelService {

    /**
     * excel导出 执行完毕请执行os.close();
     * @param templateFilePath 文件模板路径
     * @param os 输出流 根据模板填充后的输出流
     * @param model 数据
     * @throws IOException
     */
    public  void exportExcel(String templateFilePath, OutputStream os, Map<String, Object> model) throws Exception {
        File template = new File(templateFilePath);
        if (template.exists()) {
            exportExcel(new FileInputStream(template), os, model);
        }else {
            throw new Exception("Excel 模板未找到");
        }

    }

    /**
     * 浏览器导出excel 这种适合在contrllor中直接操作 这是针对模板文件存储位置所写的方法
     * @param templateFilePath 文件模板路径
     * @param response
     * @param model 数据
     * @param fileName 导出文件名
     * @throws IOException
     */
    public void webExportExcel(String templateFilePath, HttpServletResponse response, Map<String, Object> model, String fileName) throws Exception {
        ClassPathResource resource = new ClassPathResource(templateFilePath);
        try (ByteArrayOutputStream os = new ByteArrayOutputStream();
             InputStream is = resource.getInputStream()) {
            exportExcel(is, os, model);
            download(os.toByteArray(), fileName, response);
        }
    }

    /**
     * 浏览器导出excel 这种适合在contrllor中直接操作 这是针对模板文件存储位置所写的方法
     * @param templateFilePath 文件模板路径
     * @param response
     * @param model 数据
     * @param fileName 导出文件名
     * @throws IOException
     */
    public void webExportExcelNew(String templateFilePath, HttpServletResponse response, Map<String, Object> model , String fileName) throws Exception {
        ClassPathResource resource = new ClassPathResource(templateFilePath);
        try (ByteArrayOutputStream os = new ByteArrayOutputStream();
             InputStream is = resource.getInputStream()) {
            exportExcelNew(is, os, model);
            download(os.toByteArray(), fileName, response);
        }
    }

    /**
     * 文件下载，需要一个文件的InputStream
     * @param
     * @param response
     * @throws IOException
     */
    public void download(byte[] data, String fileName, HttpServletResponse response) throws IOException{
        fileName = URLEncoder.encode(fileName, Constants.UTF8);
        response.addHeader("Content-Disposition", "attachment;filename=\"" + fileName + "\"");
        response.addHeader("Content-Length", "" + data.length);
        response.setHeader("FileName", fileName);
        response.setHeader("Access-Control-Expose-Headers", "FileName");
        IOUtils.write(data, response.getOutputStream());
    }
    /**
     * excel导出 执行完毕请执行os.close();
     * @param templateFile 文件模板
     * @param os 输出流 根据模板填充后的输出流
     * @param model 数据
     * @throws IOException
     */
    public void exportExcel(File templateFile, OutputStream os, Map<String, Object> model) throws Exception {
        exportExcel(new FileInputStream(templateFile), os, model);
    }

    /**
     * excel导出 执行完毕请执行os.close();
     * @param is 输入流 excel模板文件流 文件模板
     * @param os 输出流 根据模板填充后的输出流
     * @param model 数据
     * @throws IOException
     */
    public void exportExcel(InputStream is, OutputStream os, Map<String, Object> model) throws IOException {
        Context context = new Context();
        if (model != null) {
            for (Map.Entry<String, Object> entry : model.entrySet()) {
                context.putVar(entry.getKey(), entry.getValue());
            }
        }
        JxlsHelper jxlsHelper = JxlsHelper.getInstance().setProcessFormulas(false).setEvaluateFormulas(true);
        Transformer transformer = jxlsHelper.createTransformer(is, os);
        JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig()
                .getExpressionEvaluator();
        Map<String, Object> func = new HashMap<>(16);
        // 添加自定义功能
        func.put("utils", new JxlsUtils());
        JexlEngine jexlEngine = new JexlBuilder().namespaces(func).create();
        evaluator.setJexlEngine(jexlEngine);
        jxlsHelper.processTemplate(context, transformer);
    }

    /**
     * excel导出 执行完毕请执行os.close();
     * @param is 输入流 excel模板文件流 文件模板
     * @param os 输出流 根据模板填充后的输出流
     * @param model 数据
     * @throws IOException
     */
    public void exportExcelNew(InputStream is, OutputStream os, Map<String, Object> model) throws IOException {
        Context context = new Context();
        if (model != null) {
            for (Map.Entry<String, Object> entry : model.entrySet()) {
                context.putVar(entry.getKey(), entry.getValue());
            }
        }
        JxlsHelper jxlsHelper = JxlsHelper.getInstance().setEvaluateFormulas(true);
        Transformer transformer = jxlsHelper.createTransformer(is, os);
        JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig()
                .getExpressionEvaluator();
        Map<String, Object> func = new HashMap<>(16);
        // 添加自定义功能
        func.put("utils", new JxlsUtils());
        JexlEngine jexlEngine = new JexlBuilder().namespaces(func).create();
        evaluator.setJexlEngine(jexlEngine);
        jxlsHelper.processTemplate(context, transformer);
    }

    /**
     * @param templateFilePath 模板路径
     * @param response response
     * @param context 数据
     * @param fileName 文件名
     * @param objectProps 字段
     * @param index grid位置下标
     * @throws IOException
     */
    public void gridExport(String templateFilePath, HttpServletResponse response, Context context, String fileName, String objectProps, int index) throws IOException {
        ClassPathResource resource = new ClassPathResource(templateFilePath);
        try (ByteArrayOutputStream os = new ByteArrayOutputStream();
             InputStream inputStream = resource.getInputStream()) {
            JxlsHelper jxlsHelper = JxlsHelper.getInstance().setProcessFormulas(false);
            Transformer transformer = TransformerFactory.createTransformer(inputStream, os);
            AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);
            List<Area> xlsAreaList = areaBuilder.build();
            Area xlsArea = xlsAreaList.get(0);
            GridCommand gridCommand = (GridCommand) xlsArea.getCommandDataList().get(index).getCommand();
            gridCommand.setProps(objectProps);
            xlsArea.applyAt(new CellRef("Sheet1!A1"), context);
            jxlsHelper.processTemplate(context, transformer);
            download(os.toByteArray(), fileName, response);
        }
    }

    /**
     * 只有解析
     * @param inputStream
     * @param originalFilename
     * @return
     */
    public List<List<Object>> getBankListByExcel(InputStream inputStream, String originalFilename) {
        String fileName = originalFilename.substring(originalFilename.lastIndexOf(".")+1);//StringUtils.substringBeforeLast(originalFilename, ".");
        if ("xls".equals(fileName) || "xlsx".equals(fileName)) {
            return BankListByExcel(inputStream, originalFilename);
        } else if ("csv".equals(fileName)|| "CSV".equals(fileName)) {
            return BankListByCsv(inputStream, originalFilename);
        } else if ("txt".equals(fileName)) {
            return BankListByTXT(inputStream, originalFilename);
        } else {
            return null;
        }
    }
    /**
     * 读取TXT
     *
     * @param inputStream
     * @param originalFilename
     * @return
     */
    private List<List<Object>> BankListByTXT(InputStream inputStream, String originalFilename) {
        List<List<Object>> lists = new ArrayList<>();
        List<Object> list = null;
        BufferedReader bufferdeReader = new BufferedReader(new InputStreamReader(inputStream));
        String line = "";
        try {
            line = bufferdeReader.readLine();
            String[] split = null;
            while ((line = bufferdeReader.readLine()) != null) {
                if (line.trim() != "") {
                    list = new ArrayList<>();
                    split = line.split("||");
                    for (int i = 0; i < split.length; i++) {
                        list.add(split[i]);
                    }
                    lists.add(list);
                }
            }
            //220534 不
            System.out.println("size=>"+lists.size());
            bufferdeReader.close();
            return   lists;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {


        }
        return null;
    }
    /**
     * 读取csv
     *
     * @param inputStream
     * @param originalFilename
     * @return
     */
    private List<List<Object>> BankListByCsv(InputStream inputStream, String originalFilename) {
        BufferedReader bufferdeReader = new BufferedReader(new InputStreamReader(inputStream));
        //  bReader = new BufferedReader(new InputStreamReader(new FileInputStream(file), "gbk"));
        String line = "";
        //忽略第一行标题
        List<List<Object>> lists = new ArrayList<>();
        List<Object> list = null;

        try {
            String[] split = null;
            for (int i = 0; i < 1; i++) {

                line = bufferdeReader.readLine();
                System.out.println(line);

            }
            while ((line = bufferdeReader.readLine()) != null) {
                if (line.trim() != "") {
                    //分割开来的即是对应的每个单元格，注意空的情况
                    list = new ArrayList<>();
                    // line = bufferdeReader.readLine();
                    split =line.split(",");
                    for (int i = 0; i < split.length; i++) {
                        System.out.println("split=>"+split[i]);
                        list.add(split[i]);
                    }
                    lists.add(list);
                }
            }
            bufferdeReader.close();
            return lists;

        } catch (IOException e) {
            e.printStackTrace();
        }

        return null;
    }
    private Workbook getWorkbook(InputStream inputStream, String originalFilename) throws IOException {
        Workbook workbook = null;
        String fileType = originalFilename.substring(originalFilename.lastIndexOf("."));
        if (".xls".equals(fileType)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (".xlsx".equals(fileType)) {
            workbook = new XSSFWorkbook(inputStream);
        } else {
            return null;
        }
        return workbook;
    }
    /**
     * 读取excel
     * @param inputStream
     * @param originalFilename
     * @return
     */
    private List<List<Object>> BankListByExcel(InputStream inputStream, String originalFilename) {
        List list = new ArrayList<>();
        //创建Excel工作薄
        Workbook work = null;
        String value = "";
        try {
            work = this.getWorkbook(inputStream, originalFilename);
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
        if (null == work) {
            return null;
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        boolean flag = false;
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (null == row || row.getFirstCellNum() == j) {
                    continue;
                }


                List<Object> li = new ArrayList<>();
                //System.out.println(" row.getFirstCellNum()===>"+  row.getFirstCellNum());
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    //System.out.println("cell===>"+ cell);
                    if (null != cell) {
//                        _NONE(-1),
//                                NUMERIC(0),
//                                STRING(1),
//                                FORMULA(2),
//                                BLANK(3),
//                                BOOLEAN(4),
//                                ERROR(5);
                        switch (cell.getCellType()) {
                            case NUMERIC:
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
                                    value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                                    break;
                                } else {
                                    Double d = cell.getNumericCellValue();
                                    DecimalFormat df = new DecimalFormat("#.##");
                                    value = df.format(d);
                                }
                                break;
                            case STRING:
                                value = cell.getStringCellValue();
                                break;
                            case BOOLEAN:
                                value = cell.getBooleanCellValue() + "";
                                break;
                            case FORMULA:
                                value = cell.getCellFormula() + "";
                                break;
                            case BLANK:
                                value = "";
                                break;
                            case ERROR:
                                value = "非法字符";
                                break;
                            default:
                                value = "未知类型";
                                break;
                        }

                    }
                    li.add(value);
                }
                list.add(li);
            }
        }
        try {
            work.close();
        } catch (IOException e) {

            e.printStackTrace();
            return null;
        } finally {
        }
        return list;
    }



}
